package mysql

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func init() {
	dsn := "root:root@tcp(localhost:3306)/go_db?charset=utf8mb4&parseTime=true"

	var err error
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}

	err2 := db.Ping()
	if err2 != nil {
		log.Fatal(err2.Error())
	}

	// See "Important settings" section.
	db.SetConnMaxLifetime(time.Minute * 3)
	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(10)
}

func main_6() {

	defer db.Close()

	queryData()
	insert()
	del()
}

// 删除数据
func del() {
	sql := "delete from user where id = ?"
	result, err := db.Exec(sql, 3)
	if err != nil {
		log.Fatal(err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}

	if rows != 1 {
		log.Fatalf("expected to affect 1 row, affected %d", rows)
	}
}

// 插入数据
func insert() {
	sql := "insert into user(username, passwd)  values(?,?)"
	result, err := db.Exec(sql, "leeyu", "admin")
	if err != nil {
		log.Fatal("插入失败")
	}

	// rows 影响行数
	rows, err := result.RowsAffected()

	if err != nil {
		log.Fatal(err)
	}

	if rows != 1 {
		log.Fatalf("expected to affect 1 row, affected %d", rows)
	}

	fmt.Printf("r: %v\n", rows)
}

// 查询数据
func queryData() {
	var queryName string = "tom"
	sql := "select * from user where username = ?"
	rows, err := db.Query(sql, queryName)
	if err != nil {
		log.Fatal(err)
	}

	defer rows.Close()

	for rows.Next() {
		var (
			id       int64
			username string
			passwd   string
		)
		if err := rows.Scan(&id, &username, &passwd); err != nil {
			log.Fatal(err)
		}
		log.Printf("id %d username is %s  passwd is %s \n", id, username, passwd)
	}

}
